This article is about to create a simple VBA function used to recover deleted table from a Microsoft Access. Whenever we delete the table from database window, the table isn't actually deleted although it appears to be. The table is recoverable until that particular database is open. We can recover the table by running the undo function. This article shows that how we can recover tables deleted. The recoverability is possible only when the access database is not close, access database is not compacted since the table deleted and table is deleted using user interface.

For implementation of this utility we have to create a table as shown in Fig 1.1.

Now we have to write function for recoverability. Now we have to test this function. For that we have to delete the table from Access database. After deleting the table we have to call the undo function from the immediate window of visual basic editor as shown in Fig 1.2.

As we call this function the message will prompt for recover the deleted table as shown in Fig 1.3.

As we press on restored button the deleted table will recover as shown in Fig 1.4.

VBA code

Option Compare Database
Public Function undo()
Dim strTablename As String, i As Integer, StrSqlString As String
For i = 0 To CurrentDb.TableDefs.Count - 1
If Left(CurrentDb.TableDefs(i).Name, 4) = "~tmp" Then
strTablename = CurrentDb.TableDefs(i).Name
StrSqlString = "SELECT DISTINCTROW [" & strTablename & _ "].* INTO MyUndeletedTable FROM [" & strTablename & "];"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSqlString
DoCmd.SetWarnings True
MsgBox "A table has been restored as MyUndeletedTable", _ vbOKOnly, "Restored"
End If
Next i
Exit Function
End Function